-------------------------------------------------------------------------------
-- Functions needed to support the Conversion
-------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNumericDatabaseVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetNumericDatabaseVersion]
GO

CREATE FUNCTION [dbo].[GetNumericDatabaseVersion]()
RETURNS int
AS
BEGIN
	declare @version VARCHAR(30)
	declare @numericVersion int

	select @version=VersionName
	from Version
	where Description = 'Insight'

	select @numericVersion = convert( int , 
	case when substring( @version , 1 , 1 ) LIKE '[0-9]' then substring( @version , 1 , 1 ) else '' end +
	case when substring( @version , 2 , 1 ) LIKE '[0-9]' then substring( @version , 2 , 1 ) else '' end +
	case when substring( @version , 3 , 1 ) LIKE '[0-9]' then substring( @version , 3 , 1 ) else '' end +
	case when substring( @version , 4 , 1 ) LIKE '[0-9]' then substring( @version , 4 , 1 ) else '' end +
	case when substring( @version , 5 , 1 ) LIKE '[0-9]' then substring( @version , 5 , 1 ) else '' end +
	case when substring( @version , 6 , 1 ) LIKE '[0-9]' then substring( @version , 6 , 1 ) else '' end +
	case when substring( @version , 7 , 1 ) LIKE '[0-9]' then substring( @version , 7 , 1 ) else '' end +
	case when substring( @version , 8 , 1 ) LIKE '[0-9]' then substring( @version , 8 , 1 ) else '' end +
	case when substring( @version , 9 , 1 ) LIKE '[0-9]' then substring( @version , 9 , 1 ) else '' end
	)

	return  @numericVersion
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDatabaseVersion]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDatabaseVersion]
GO

CREATE FUNCTION [dbo].[FormatDatabaseVersion]
(
	@numericVersion int
)
RETURNS VARCHAR(30)
AS
BEGIN

	declare @version varchar(30)
    set @version = cast(@numericVersion as varchar)
    set @version =
		SUBSTRING(@version, 1, 1) + '.' +
		SUBSTRING(@version, 2, 1) + '.' +
		SUBSTRING(@version, 3, 1) + '.' +
		SUBSTRING(@version, 4, 3)

	return @version
END
GO

-------------------------------------------------------------------------------
-- Setup the version for 2.3.2 release
-------------------------------------------------------------------------------
BEGIN

	IF ( [dbo].[GetNumericDatabaseVersion]() < 232000 )
	BEGIN

	declare @dbversion	VARCHAR(30)
	set @dbversion = null

		BEGIN
			set @dbversion = '2.3.2.000'
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END -- End of version setup

END
GO

-------------------------------------------------------------------------------
-- From here on, all the new changes will be added for 2.3.2 release
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.2.001
-------------------------------------------------------------------------------

BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232001 )
	BEGIN

		-- sp646 TFS# 509(TrackIt #9768 - Add Module to Application Logging for improved utilization reporting)
		-- This table will store all the module names the users have visited in the application.
			IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Activity_Modules]') AND type in (N'U'))
			BEGIN

			CREATE TABLE [dbo].[Activity_Modules](
				[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
				[UserName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[ModuleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
			) ON [PRIMARY]

			END 

		BEGIN
			declare @dbversion varchar(30)
			set @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
            update Version
			set VersionName = @dbversion
			where Description = 'Insight'

		    PRINT 'Insight database version now ' + @dbversion
		END

	END

END -- End 2.3.2.001


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.2.002
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232002 )
      BEGIN

            -- TFS# 533 and 535(TrackIt #8611, #7386 - COI - Add Hierarchy Type to the Hierarchy Maintenance and supervisor summary page.
            -- This table will store all the supported Hierarchy Types for COI.
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COIHierarchyType]') AND type in (N'U'))
            BEGIN
                  CREATE TABLE [dbo].[COIHierarchyType](
                        [ID] [int] IDENTITY(1,1) NOT NULL,
                        [Description] [VARCHAR](100) NOT NULL
                   CONSTRAINT [PK_COIHierarchyType_1] PRIMARY KEY CLUSTERED 
                  (
                        [ID] ASC
                  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                  ) ON [PRIMARY]
            END
            BEGIN
                  --Adding Rows to the COIHierarchyType
                  EXEC('INSERT INTO COIHierarchyType (Description) VALUES (''Board'')')
                  EXEC('INSERT INTO COIHierarchyType (Description) VALUES (''Committee'')')
                  EXEC('INSERT INTO COIHierarchyType (Description) VALUES (''Institutional'')')
            END
            BEGIN
            --Adding Column to the COI_Hierarchy_Map table.
                  EXEC('ALTER TABLE COI_Hierarchy_Map
                  ADD HierarchyTypeId INT
                  CONSTRAINT FK_COIHierarchyMap_HierarchyTypeId REFERENCES COIHierarchyType(Id)')
            END
            BEGIN
            --Updating the COI_Hierarchy_Map table's HierarchyTypeId to Institutional value of 3.
                  EXEC('UPDATE COI_Hierarchy_Map
                  SET HierarchyTypeId = 3')
            END
            BEGIN
                  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
				  UPDATE Version
                  SET VersionName = @dbversion
                  WHERE Description = 'Insight'

                PRINT 'Insight database version now ' + @dbversion
            END

      END

END -- End 2.3.2.002


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 2.3.2.003
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232003 )
      BEGIN
            -- TFS# 393 Setting for more variations on Animals SRAC Intake and PreReview SignOff.
			INSERT INTO Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			VALUES ('SRACPreView', 15531, 2, 'Default')

			INSERT INTO Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			VALUES ('SRACPreView', 192977, 2, 'Default')

			INSERT INTO Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			VALUES ('SRACPreView', 26520, 2, 'Default')

			INSERT INTO Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			VALUES ('SRACIntakeAR', 192977, 2, 'Default')

			INSERT INTO Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			VALUES ('SRACIntakeAR', 10319, 2, 'Default')

			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACIntakeTissue', 28380, 2, 'Default')

			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACIntakeTissue', 26520, 2, 'Default')

			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACPreViewAMEStaffN', 10319, 2, 'Default')

			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACPreViewAMEStaffN', 15531, 2, 'Default')


			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACPreViewAMEStaffY', 28380, 2, 'Default')

			insert into Organization_WorkflowRole_Person_Map (WorkflowRole, PersonId, OrganizationId, WorkflowType)
			values ('SRACPreViewAMEStaffY', 10319, 2, 'Default')
			
			INSERT INTO RefCode 
			VALUES ('ANIMALS_WORKFLOW','EIACUC_STDTSUE_WF','Animals Standard Tissue Workflow','Y')
			
			BEGIN
				  --Adding Column to the ProtocolProcess table.
                  EXEC('alter table ProtocolProcess
                  add MovedToRex int')
            END
            
            BEGIN
				EXEC('update ProtocolProcess 
				set MovedToRex = 1 
				where RexIrbStatusId != 0')

				EXEC('update pp 
					set pp.MovedToRex = 0
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''h''
					and RexIrbStatusId = 0')

				EXEC('update pp 
				set pp.MovedToRex = 1
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''a''
					and pr.ProtocolNumber is not null
				and pp.name = ''IR''')

				EXEC('update pp 
				set pp.MovedToRex = 0
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''a''
					and pr.ProtocolNumber is null
				and pp.name = ''IR''')

				EXEC('update pp 
				set pp.MovedToRex = 0
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''a''
					and pr.ProtocolNumber is null
				and pp.status = ''PPSTATUS_WIP''')

				EXEC('update pp 
				set pp.MovedToRex = 0
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''a''
					and pr.ProtocolNumber is not null
				and pp.status = ''PPSTATUS_WIP''')

				EXEC('update pp 
				set pp.MovedToRex = 0
					from Protocol pr
					inner join ProtocolProcess pp
					on pp.ProtocolId = pr.Id
					where pr.MOduleType = ''a''
					and pr.ProtocolNumber is not null
				and pp.status = ''PPSTATUS_SUBMT''')
						
				EXEC('update pp 
				set pp.MovedToRex = 1
				from ProtocolProcess pp
				where pp.MovedToRex is null')
				
				EXEC('
				update pp
				set pp.MovedToRex = 0
				from ProtocolProcess pp
				inner join Protocol pt
				on pt.Id = pp.ProtocolId
				where moduletype = ''a'' 
				and pp.Status = ''PPSTATUS_PEND''
				and MovedToRex = 1')
				
				EXEC('
				update pp
				set pp.MovedToRex = 1
				from ProtocolProcess pp
				inner join Protocol pt
				on pt.Id = pp.ProtocolId
				inner join ProtocolProcess_Workflow_map ppw
				on ppw.ProtocolProcessId = pp.Id
				inner join swexecutionDetails sw
				on sw.ExecutionId = ppw.ExecutionId
				where moduletype = ''a'' 
				and MovedToRex = 0 
				and sw.ActionName = ''MoveToRex''
				')
				
				EXEC('
				update pp
				set pp.MovedToRex = 1
				from ProtocolProcess pp
				inner join Protocol pt
				on pt.Id = pp.ProtocolId
				inner join ProtocolProcess_Workflow_map ppw
				on ppw.ProtocolProcessId = pp.Id
				inner join swexecutionDetails sw
				on sw.ExecutionId = ppw.ExecutionId
				where moduletype = ''a''
				and MovedToRex = 0 
				and sw.ActionType = ''MoveToRex''
				')				
      END
      
      BEGIN
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

            PRINT 'Insight database version now ' + @dbversion
            END
      END

END -- End 2.3.2.003

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.004
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232004 )
      BEGIN
		-- TFS# 397 (TrackIt #8624 - Provide a way for COI participants to update COI information real-time without disrupting the Annual Cycle.
		-- This table will store the ConflictOfInterset Types.
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COIType]') AND type in (N'U'))
		BEGIN
			  CREATE TABLE [dbo].[COIType](
					[ID] [int] IDENTITY(1,1) NOT NULL,
					[Description] [VARCHAR](100) NOT NULL
			   CONSTRAINT [PK_COIType_1] PRIMARY KEY CLUSTERED 
			  (
					[ID] ASC
			  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			  ) ON [PRIMARY]
		END
		BEGIN
			  --Adding Rows to the COIType
			  EXEC('INSERT INTO COIType (Description) VALUES (''Annual Cycle'')')
			  EXEC('INSERT INTO COIType (Description) VALUES (''Real Time Updates'')')
		END
		BEGIN
		--Adding Column to the ConflictOfInterest table.
			  EXEC('ALTER TABLE ConflictOfInterest
			  ADD COITypeId INT
			  CONSTRAINT FK_COIType_COITypeId REFERENCES COIType(Id)')
		END
		BEGIN
		--Updating the ConflictOfInterest
			  EXEC('UPDATE ConflictOfInterest
			  SET COITypeId = 1')
		END
		--Adding the COIAudit table
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COIAudit]') AND type in (N'U'))
		BEGIN
			  CREATE TABLE [dbo].[COIAudit](
					[ID] [INT] IDENTITY(1,1) NOT NULL,
					[COIId] [INT] NOT NULL,
					[Action] [VARCHAR](200) NOT NULL,
					[AdmPersonId] [INT] NOT NULL ,
					[ActionDate] [DATETIME] DEFAULT GETDATE(),
			   CONSTRAINT [PK_COIAudit] PRIMARY KEY CLUSTERED 
			  (
					[ID] ASC
			  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			  ) ON [PRIMARY]

		            
		END
		BEGIN
			  EXEC('ALTER TABLE [dbo].[COIAudit] WITH CHECK ADD  CONSTRAINT [FK_COIAudit_AdmPerson] FOREIGN KEY([AdmPersonId]) REFERENCES [dbo].[AdmPerson] ([Id])')
		END
		BEGIN
			  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			  UPDATE Version
			  SET VersionName = @dbversion
			  WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END
END -- End 2.3.2.004			


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.005
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232005 )
      BEGIN
		--dm853 trackit 401, 427 change to add ParentDocId for attachments and single species for PDF
		--creation. 
		BEGIN
			  EXEC('ALTER TABLE ProtocolDocument ADD ParentDocumentId INT')
		END
		
		BEGIN
			--deletes invalid documents
			EXEC('DELETE FROM ProtocolDocument where documentId is null and type = ''0'' and name = ''''')

			EXEC('DELETE FROM ProtocolDocument where type = ''0'' and name = ''''')

			EXEC('DELETE FROM ProtocolDocument where type = ''0'' and DocumentId is null')

			EXEC('DELETE FROM ProtocolDocument where type = ''0''')

			EXEC('DELETE FROM ProtocolDocument where DocumentId is null and HardCopy = 0')

			--updates new field with the right values.
			EXEC('DECLARE @protocolId INT

			DECLARE doc_cursor CURSOR FOR
			SELECT DISTINCT ProtocolId
			FROM ProtocolDocument 
			ORDER BY ProtocolId

			OPEN doc_cursor

			FETCH NEXT FROM doc_cursor 
			INTO @protocolId

			WHILE @@FETCH_STATUS = 0
			BEGIN   	
							UPDATE pd
							SET pd.ParentDocumentId = pd2.Id
							FROM ProtocolDocument pd
							INNER JOIN ProtocolDocument pd2
							ON pd2.ProtocolId = pd.ProtocolId
							AND pd2.Name = pd.Name 
							AND	pd2.type = pd.type
							WHERE  pd.ProtocolId = @protocolId				

				FETCH NEXT FROM doc_cursor 
				INTO @protocolId
			END

			CLOSE doc_cursor
			DEALLOCATE doc_cursor')
		END

		BEGIN
			  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			  UPDATE Version
			  SET VersionName = @dbversion
			  WHERE Description = 'Insight'
			  

			PRINT 'Insight database version now ' + @dbversion
		END
		
	END
END -- End 2.3.2.005	


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.006
-------------------------------------------------------------------------------

BEGIN
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232006 )
	BEGIN
		-- sp646 TFS# 1071: Advance Fund Request Enhancement
		-- These tables are added to Insight for the Advance Fund Request.			
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequest]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequest](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[PropNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[DateSubmitted] [datetime] NULL,
				[SubmittedBy] [int] NULL,
				[DateCreated] [datetime] NOT NULL CONSTRAINT [DF__AdvanceFu__DateC__70DE233D]  DEFAULT (getdate()),
				[CreatedBy] [int] NOT NULL,
				[FundNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 			CONSTRAINT [PK_AdvanceFundRequest] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]
		END 
			
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestComments]') AND type in (N'U'))
		BEGIN	
			CREATE TABLE [dbo].[AdvanceFundRequestComments](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[AdvanceFundRequestId] [int] NOT NULL,
				[AdvanceFundRequestStatusTypeId] [int] NOT NULL,
				[Comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[CommentedBy] [int] NOT NULL,
				[DateCommented] [datetime] NOT NULL,
			CONSTRAINT [PK_AdvanceFundRequestComments] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestEmailNotification]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestEmailNotification](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[AdvanceFundRequestId] [int] NOT NULL,
				[AdvanceFundRequestStatusTypeId] [int] NOT NULL,
				[EmailedTo] [int] NOT NULL,
				[EmailedBy] [int] NOT NULL,
				[Subject] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[MessageBody] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[DateEmailed] [datetime] NOT NULL CONSTRAINT [DF_AdvanceFundRequestEmailNotification_DateEmailed]  DEFAULT (getdate()),
			 CONSTRAINT [PK_AdvanceFundRequestEmailNotification] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]		
		END		
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestEvent]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestEvent](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[AdvanceFundRequestEventTypeId] [int] NOT NULL,
				[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			 CONSTRAINT [PK_AdvanceFundRequestEvent_1] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]		
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestEventHistory]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestEventHistory](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[AdvanceFundRequestId] [int] NOT NULL,
				[AdvanceFundRequestEventId] [int] NOT NULL,
				[CreatedBy] [int] NOT NULL,
				[DateCreated] [datetime] NOT NULL,
			 CONSTRAINT [PK_AdvanceFundRequestEvent] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]		
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestEventType]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestEventType](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			 CONSTRAINT [PK_AdvanceFundRequestEventType] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestStatusType]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestStatusType](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
			 CONSTRAINT [PK_AdvanceFundRequestStatusType] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestUploadedDocument]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestUploadedDocument](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[PropNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
				[DateUploaded] [datetime] NOT NULL DEFAULT (getdate()),
				[UploadedBy] [int] NOT NULL,
				[DocumentName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
			) ON [PRIMARY]
			
		END
		BEGIN			
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestUploadedDocument] ADD [FileUrl] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL')
		END
		BEGIN	
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestUploadedDocument] ADD  CONSTRAINT [PK_AdvanceFundRequestUploadedDocument] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]')
		END
		
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AdvanceFundRequestWorkFlowStatus]') AND type in (N'U'))
		BEGIN
			CREATE TABLE [dbo].[AdvanceFundRequestWorkFlowStatus](
				[Id] [int] IDENTITY(1,1) NOT NULL,
				[AdvanceFundRequestId] [int] NOT NULL,
				[AdvanceFundRequestStatusTypeId] [int] NOT NULL,
				[DateStart] [datetime] NOT NULL DEFAULT (getdate()),
				[CreatedBy] [int] NOT NULL,
				[NoOfReminder] [int] NULL CONSTRAINT [DF_AdvanceFundRequestWorkFlowStatus_NoOfReminder]  DEFAULT ((0)),
			 CONSTRAINT [PK_AdvanceFundRequestWorkFlowStatus] PRIMARY KEY CLUSTERED 
			(
				[Id] ASC
			)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
			) ON [PRIMARY]
		END	
		BEGIN	
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestWorkFlowStatus]  WITH CHECK ADD  CONSTRAINT [FK_AdvanceFundRequestWorkFlowStatus_AdvanceFundRequest] FOREIGN KEY([AdvanceFundRequestId])
			REFERENCES [dbo].[AdvanceFundRequest] ([Id])')
		END
		BEGIN
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestWorkFlowStatus] CHECK CONSTRAINT [FK_AdvanceFundRequestWorkFlowStatus_AdvanceFundRequest]')
		END
		BEGIN
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestWorkFlowStatus]  WITH CHECK ADD  CONSTRAINT [FK_AdvanceFundRequestWorkFlowStatus_AdvanceFundRequestStatusType] FOREIGN KEY([AdvanceFundRequestStatusTypeId])
			REFERENCES [dbo].[AdvanceFundRequestStatusType] ([Id])')
		END
		BEGIN
			EXEC('ALTER TABLE [dbo].[AdvanceFundRequestWorkFlowStatus] CHECK CONSTRAINT [FK_AdvanceFundRequestWorkFlowStatus_AdvanceFundRequestStatusType]')
		END
		
		BEGIN
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Draft'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Awaiting PI Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Awaiting Department Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Awaiting GA Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Completed'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestStatusType (Name)  VALUES (''Denied'')')	
			
			EXEC('INSERT INTO dbo.AdvanceFundRequestEventType (Description)  VALUES (''WorkFlow'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEventType (Description)  VALUES (''Comment'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEventType (Description)  VALUES (''Documentation'')')	
			
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''Draft'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''Submission'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''PI Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''Department Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''GA Signoff'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (1, ''Completed'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (2, ''Upload'')')
			EXEC('INSERT INTO dbo.AdvanceFundRequestEvent (AdvanceFundRequestEventTypeId, Name)  VALUES (2, ''Delete'')')
		END
		
		BEGIN
		  	SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
		  	UPDATE Version
		  	SET VersionName = @dbversion
		  	WHERE Description = 'Insight'
		  	
		  PRINT 'Insight database version now ' + @dbversion
		END
	END	
END -- End 2.3.2.006

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.007
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232007 )
      BEGIN
		-- TFS# 397 (TrackIt #8624 - Provide a way for COI participants to update COI information real-time without disrupting the Annual Cycle.
		-- This table will store the ConflictOfInterset Types.
		BEGIN
		--Adding Column to the ConflictOfInterest table.
			  EXEC('ALTER TABLE COIReportingPeriods
					ADD COITypeId INT
					CONSTRAINT FK_COIReportingPeriods_COITypeId REFERENCES COIType(Id)')
		END
		BEGIN
		--Updating the COIReportingPeriods
			  EXEC('UPDATE COIReportingPeriods
					SET COITypeId = 1')
		END
		BEGIN
			EXEC('INSERT INTO COIReportingPeriods (ReportingPeriod,COITypeId)
			VALUES(''Real time updates 2009'',2)')
		END
		BEGIN
			  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			  UPDATE Version
			  SET VersionName = @dbversion
			  WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END
END -- End 2.3.2.007	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.008
-------------------------------------------------------------------------------

BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232008 )
      BEGIN
			-- sp646 TFS# 1071: Advance Fund Request Enhancement
			BEGIN
				EXEC('INSERT INTO dbo.admRoleTypes (Name)  VALUES (''AFR-DeptChief-Delegate'')')
			END
			
			BEGIN
		  		SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
		  		UPDATE Version
		  		SET VersionName = @dbversion
		  		WHERE Description = 'Insight'
			  	
			  PRINT 'Insight database version now ' + @dbversion
			END
      END
END  -- End 2.3.2.008	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.009
-------------------------------------------------------------------------------
 
BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232009 )
      BEGIN
		-- TFS# 518 (TrackIt #8534 - Single sign on interaction between Insight and InfoEd.
		-- VRG4 		
		BEGIN
		-- This table is used for logging the incoming requests to Insight from InfoEd.
			IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InfoEdToInsightLog]') AND type in (N'U'))
			BEGIN

				  CREATE TABLE [dbo].[InfoEdToInsightLog](
						[ID] [int] IDENTITY(1,1) NOT NULL,
						[RemoteUser] [VARCHAR](36),
						[TargetPage] [VARCHAR](40),
						[QueryString][VARCHAR](100),
						[Url] [VARCHAR](100),
						[DateCreated] [DATETIME] DEFAULT GETDATE(),
				   CONSTRAINT [PK_InfoEdToInsightLog_1] PRIMARY KEY CLUSTERED 
				  (
						[ID] ASC
				  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
				  ) ON [PRIMARY]
			END
		END
		BEGIN
			  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			  UPDATE Version
			  SET VersionName = @dbversion
			  WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END
END -- End 2.3.2.009	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.010
-------------------------------------------------------------------------------
 
BEGIN
	--VRG4
	-- TFS# 533 and 535(TrackIt #8611, #7386 - COI - Add Hierarchy Type to the Hierarchy Maintenance and supervisor summary page.
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232010 )
	BEGIN
		BEGIN
			-- NOT NULL Constraint after Table is created
			EXEC('ALTER TABLE COI_Hierarchy_Map
				ALTER COLUMN HierarchyTypeId INT NOT NULL')
		END
		BEGIN
			--Dropping the Constraint
			EXEC('ALTER TABLE COI_Hierarchy_Map
			DROP CONSTRAINT [PK_ParentId_ChildID]')
		END
		BEGIN
			--ReCreating the Constraint.
			EXEC('ALTER TABLE COI_Hierarchy_Map
				ADD CONSTRAINT [PK_ParentId_ChildID] PRIMARY KEY CLUSTERED 
				(
					[ParentId] ASC,
					[ChildId] ASC,
					[HierarchyTypeId]
				)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
				)
		END
		BEGIN
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END		
END -- End 2.3.2.010	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.011
-------------------------------------------------------------------------------
 
BEGIN
	--DM853
	-- adding one more category for the Questionnaire in Humans
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232011 )
	BEGIN
		BEGIN				
				EXEC('insert into RefCode (RefDomain, RefCode, RefMeaning, Active)
				values (''QUESTCAT_CF'',''QUESTCAT_CF_CC'',''Centers/Cores'',''Y'')')
		END
		BEGIN
				EXEC ('insert into RefCodeAttribute 
				values (''QUESTCAT_CF'',''QUESTCAT_CF_CC'',''Intervention'',''Y'')')
		END
		BEGIN
				EXEC ('insert into RefCodeAttribute 
				values (''QUESTCAT_CF'',''QUESTCAT_CF_CC'',''Multiple'',''0'')')
		END
		BEGIN
				EXEC ('insert into RefCodeAttribute 
				values (''QUESTCAT_CF'',''QUESTCAT_CF_CC'',''Order'',''15'')')
		END
		
		BEGIN
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END		
END -- End 2.3.2.011	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.012
-------------------------------------------------------------------------------
 
BEGIN
	--JML29
	--12-29-2009 sc # 3509699 add new Attachment Types.
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232012 )
	BEGIN
		if not exists (select * from RefCode where RefDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and RefMeaning='Statement of Work')
		INSERT INTO RefCode (RefDomain,RefCode,RefMeaning,Active) values 
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','Statement of Work','Y')

		if not exists (select * from RefCode where RefDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and RefMeaning='Manual of Operations')
		INSERT INTO RefCode (RefDomain,RefCode,RefMeaning,Active) values 
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','Manual of Operations','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and attribute='humans')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','HUMANS','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and attribute='humans')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','HUMANS','Y')

		BEGIN
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END		
END -- End 2.3.2.012	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.013
-------------------------------------------------------------------------------
 
BEGIN
      IF ( [dbo].[GetNumericDatabaseVersion]() < 232013 )
      BEGIN
		-- TFS# 1206 (Tech Design: Related to work item 400 - TrackIt #9680 - Update the forms / questions for the Calendar Year 2009 Cycle. This may or may not involve coding changes.)
		-- VRG4 		
		BEGIN
			UPDATE RefCodeAttribute
			SET
			Value = 'Your Associations and Equity Interests with a Non-Partners Entity'
			WHERE
			RefDomain = 'QUESTCAT_COI'
			AND 
			RefCode = 'QUESTCAT_COI_EI'
			AND
			Attribute = 'CategoryHeadingForTrustee'
		END
		BEGIN
			  SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			  UPDATE Version
			  SET VersionName = @dbversion
			  WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END
END -- End 2.3.2.013	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 2.3.2.014
-------------------------------------------------------------------------------
BEGIN
	--JML29
	--01-12-2010 sc # 3509699 add new Attachment Types. - ADDITIONAL refCodeAttributes
	IF ( [dbo].[GetNumericDatabaseVersion]() < 232014 )
	BEGIN
		if not exists (select * from RefCode where RefDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and RefMeaning='Statement of Work')
		INSERT INTO RefCode (RefDomain,RefCode,RefMeaning,Active) values 
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','Statement of Work','Y')

		if not exists (select * from RefCode where RefDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and RefMeaning='Manual of Operations')
		INSERT INTO RefCode (RefDomain,RefCode,RefMeaning,Active) values 
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','Manual of Operations','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and attribute='humans')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','HUMANS','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and attribute='humans')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','HUMANS','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and attribute='APP_INCLUDE')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','APP_INCLUDE','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and attribute='APP_INCLUDE')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','APP_INCLUDE','Y')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_STATEMENTOFW' and attribute='HUMANSORDER')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_STATEMENTOFW','HUMANSORDER','37')

		if not exists (select * from refCodeAttribute where refDomain='ATTACHMENTTYPE' and RefCode='ATCHTYPE_MANUALOFOPER' and attribute='HUMANSORDER')
		insert into refCodeAttribute (RefDomain,RefCode,Attribute,Value) values
		('ATTACHMENTTYPE','ATCHTYPE_MANUALOFOPER','HUMANSORDER','59')

		BEGIN
			SET @dbversion = [dbo].[FormatDatabaseVersion]([dbo].[GetNumericDatabaseVersion]() + 1)
			UPDATE Version
			SET VersionName = @dbversion
			WHERE Description = 'Insight'

			PRINT 'Insight database version now ' + @dbversion
		END
	END		
END -- End 2.3.2.014	
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
